package com.yss.sofa.report.engine.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.Serializable;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Arrays;
import java.util.Collection;
import java.util.HashMap;
import java.util.Map;
import java.util.Set;
import java.util.UUID;

import org.apache.commons.compress.archivers.zip.Zip64Mode;
import org.apache.commons.compress.archivers.zip.ZipArchiveEntry;
import org.apache.commons.compress.archivers.zip.ZipArchiveOutputStream;
import org.apache.commons.io.FileUtils;
import org.apache.commons.io.IOUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.FormulaError;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.yss.sofa.report.engine.Const;
import com.yss.sofa.report.engine.Report;
import com.yss.sofa.report.engine.cell.page.PageCell;
import com.yss.sofa.report.engine.cell.visible.result.ResultCell;
import com.yss.sofa.report.engine.core.Transmission;
import com.yss.sofa.report.engine.dbf.DBFDataType;
import com.yss.sofa.report.engine.dbf.DBFException;
import com.yss.sofa.report.engine.dbf.DBFField;
import com.yss.sofa.report.engine.dbf.DBFReader;
import com.yss.sofa.report.engine.dbf.DBFUtils;
import com.yss.sofa.report.engine.dbf.DBFWriter;
import com.yss.sofa.report.engine.dbf.StandardCharsets;

/**
 * 报表数据导出到dbf文件
 * @author wangyh
 * @version 2.6.X
 * @since 2.6.X
 */
public final class DbfUtil {

	private static final String DBF = ".dbf";
	public static final String ZIP = ".zip";
	private static final String TMP = ".tmp";
	private static final String ENCODING = "GBK";

	private DbfUtil() {
	}

	/**
	 * 将report对象转换为dbf文件或zip文件,直接带文件名.
	 * @param report  report对象
	 * @param filename   文件名(可包含路径)
	 * @throws Exception
	 */
	public static void export(Report report, String filename) throws Exception {
		FileOutputStream out = new FileOutputStream(new File(filename));
		try {
			export(report, out);
			out.flush();
		} finally {
			if (out != null) {
				out.close();
			}
		}
	}

	/**
	 * 处理单个sheet
	 * @param report 报告对象
	 * @param to 某个sheet
	 * @param files 临时dbf文件
	 * @throws Exception
	 */
	public static void singleHandler(Report report, String to, Map<String, String> files) throws Exception {
		int rowCount = report.getRowCount(to);
		int colCount = report.getColCount(to);
		int beginRow = 1;
		Serializable bRow = report.getParams().get(Const.INNER_REPORT_DOWNLOAD_BEGIN_ROW);
		if (bRow != null) {
			beginRow = (Integer) bRow;
		}
		Map<Integer, Integer> colMaxLength = new HashMap<Integer, Integer>();
		for (int i = 0; i < colCount; i++) {
			colMaxLength.put(i, 1);
		}

		PageCell page = report.getPage(to);

		Workbook excel = report.getExcel(false);
		FormulaEvaluator evaluator = excel.getCreationHelper().createFormulaEvaluator();
		Sheet sheet = excel.getSheet(to);
		String[][] raw = new String[rowCount][colCount];
		Map<Integer, DBFDataType> dataType = new HashMap<Integer, DBFDataType>();
		Map<Integer, String> titles = new HashMap<Integer, String>();
		// 取标题和取数据类型
		boolean isNeedGetTitle = false;
		if (beginRow > 0) {
			isNeedGetTitle = true;
		}

		for (int r = 0; r < rowCount; r++) {
			if (isNeedGetTitle && (r == (beginRow - 1))) {
			} else if (r < beginRow) {
				continue;
			}

			for (int c = 0; c < colCount; c++) {
				ResultCell rc = report.getCell(to, r, c);
				Cell cell = sheet.getRow(r).getCell(c);
				if ((r == (beginRow - 1)) || (r == beginRow)) {
					if (rc != null && rc.getDataType() == Const.CELL_DATA_TYPE_DATE) {
						if (isNeedGetTitle && (r == (beginRow - 1))) {
							titles.put(c, "C" + c);
						} else if (r == beginRow) {
							dataType.put(c, DBFDataType.DATE);
						}
					} else if (rc != null && rc.getDataType() == Const.CELL_DATA_TYPE_INT) {
						if (isNeedGetTitle && (r == (beginRow - 1))) {
							titles.put(c, "C" + c);
						} else if (r == beginRow) {
							dataType.put(c, DBFDataType.LONG);
						}
					} else {
						CellType cellType = cell.getCellTypeEnum();
						switch (cellType) {
						case BLANK:
							if (isNeedGetTitle && (r == (beginRow - 1))) {
								titles.put(c, "C" + c);
							} else if (r == beginRow) {
								dataType.put(c, DBFDataType.CHARACTER);
							}
							break;
						case BOOLEAN:
							if (isNeedGetTitle && (r == (beginRow - 1))) {
								titles.put(c, "C" + c);
							} else if (r == beginRow) {
								dataType.put(c, DBFDataType.CHARACTER);
							}
							break;
						case ERROR:
							if (isNeedGetTitle && (r == (beginRow - 1))) {
								titles.put(c, "C" + c);
							} else if (r == beginRow) {
								dataType.put(c, DBFDataType.CHARACTER);
							}
							break;
						case FORMULA:
							if (!page.isIncludeFormula()) {
								CellValue cv = evaluator.evaluate(cell);
								switch (cv.getCellTypeEnum()) {
								case BLANK:
									if (isNeedGetTitle && (r == (beginRow - 1))) {
										titles.put(c, "C" + c);
									} else if (r == beginRow) {
										dataType.put(c, DBFDataType.CHARACTER);
									}
									break;
								case BOOLEAN:
									if (isNeedGetTitle && (r == (beginRow - 1))) {
										titles.put(c, "C" + c);
									} else if (r == beginRow) {
										dataType.put(c, DBFDataType.CHARACTER);
									}
									break;
								case ERROR:
									if (isNeedGetTitle && (r == (beginRow - 1))) {
										titles.put(c, "C" + c);
									} else if (r == beginRow) {
										dataType.put(c, DBFDataType.CHARACTER);
									}
									break;
								case NUMERIC:
									dataType.put(c, DBFDataType.NUMERIC);
									if (isNeedGetTitle && (r == (beginRow - 1))) {
										titles.put(c, String.valueOf(cv.getNumberValue()));
									} else if (r == beginRow) {
										dataType.put(c, DBFDataType.NUMERIC);
									}
									break;
								case STRING:
									if (isNeedGetTitle && (r == (beginRow - 1))) {
										titles.put(c, cv.getStringValue());
									} else if (r == beginRow) {
										dataType.put(c, DBFDataType.CHARACTER);
									}
									break;
								default:
									if (isNeedGetTitle && (r == (beginRow - 1))) {
										titles.put(c, cv.formatAsString());
									} else if (r == beginRow) {
										dataType.put(c, DBFDataType.CHARACTER);
									}
									break;
								}
							} else {
								if (isNeedGetTitle && (r == (beginRow - 1))) {
									titles.put(c, "C" + c);
								} else if (r == beginRow) {
									dataType.put(c, DBFDataType.CHARACTER);
								}
							}
							break;
						case NUMERIC:
							if (isNeedGetTitle && (r == (beginRow - 1))) {
								titles.put(c, String.valueOf(cell.getNumericCellValue()));
							} else if (r == beginRow) {
								dataType.put(c, DBFDataType.NUMERIC);
							}
							break;
						case STRING:
							if (isNeedGetTitle && (r == (beginRow - 1))) {
								titles.put(c, cell.getStringCellValue());
							} else if (r == beginRow) {
								dataType.put(c, DBFDataType.CHARACTER);
							}
							break;
						default:
							if (isNeedGetTitle && (r == (beginRow - 1))) {
								titles.put(c, cell.getStringCellValue());
							} else if (r == beginRow) {
								dataType.put(c, DBFDataType.CHARACTER);
							}
							break;
						}
					}
				}
				raw[r][c] = (rc != null && rc.getDataType() == Const.CELL_DATA_TYPE_DATE) ? Transmission.getExcelCellFormattedValue(cell, evaluator).replaceAll("\"", "") : Transmission
						.getExcelCellFormattedValue(cell, evaluator);
				int len = getColLength(raw[r][c]);
				if (len > colMaxLength.get(c)) {
					colMaxLength.put(c, len);
				}
			}
		}
		DBFField fields[] = new DBFField[colCount];
		if (isNeedGetTitle) {
			for (int i = 0; i < colCount; i++) {
				String title = titles.get(i);
				if (!isAscii(title)) {
					titles.put(i, "C" + i);
				}
			}
			for (int i = 0; i < colCount; i++) {
				fields[i] = new DBFField();
				fields[i].setName(titles.get(i));
				if (dataType.get(i) == DBFDataType.LONG) {
					fields[i].setType(DBFDataType.NUMERIC);
					fields[i].setLength(colMaxLength.get(i) + 2);
				} else if (dataType.get(i) == DBFDataType.NUMERIC) {
					fields[i].setType(DBFDataType.NUMERIC);
					fields[i].setLength(colMaxLength.get(i) + 2);
					fields[i].setDecimalCount(2);
				} else if (dataType.get(i) == DBFDataType.DATE) {
					fields[i].setType(DBFDataType.DATE);
				} else {
					fields[i].setType(dataType.get(i));
					fields[i].setLength(colMaxLength.get(i));
				}
			}
		} else {
			for (int i = 0; i < colCount; i++) {
				fields[i] = new DBFField();
				fields[i].setName("C" + i);
				if (dataType.get(i) == DBFDataType.LONG) {
					fields[i].setType(DBFDataType.NUMERIC);
					fields[i].setLength(colMaxLength.get(i) + 2);
				} else if (dataType.get(i) == DBFDataType.NUMERIC) {
					fields[i].setType(DBFDataType.NUMERIC);
					fields[i].setLength(colMaxLength.get(i) + 2);
					fields[i].setDecimalCount(2);
				}else if (dataType.get(i) == DBFDataType.DATE) {
					fields[i].setType(DBFDataType.DATE);
				}  else {
					fields[i].setType(dataType.get(i));
					fields[i].setLength(colMaxLength.get(i));
				}
			}
		}

		String filePath = getTemporaryFile(DBF);
		File file = new File(filePath);
		FileOutputStream fos = new FileOutputStream(file);
		DBFWriter dbfWriter = new DBFWriter(file, StandardCharsets.GBK);
		dbfWriter.setFields(fields);

		for (int r = 0; r < rowCount; r++) {
			if (r < beginRow)
				continue;
			Object rowData[] = new Object[colCount];
			for (int c = 0; c < colCount; c++) {
				ResultCell rc = report.getCell(to, r, c);
				Cell cell = sheet.getRow(r).getCell(c);
				if (rc != null && rc.getDataType() == Const.CELL_DATA_TYPE_DATE) {
					rowData[c] = cell.getDateCellValue();
				} else if (rc != null && rc.getDataType() == Const.CELL_DATA_TYPE_INT) {
					rowData[c] = cell.getNumericCellValue();
				} else {
					CellType cellType = cell.getCellTypeEnum();
					switch (cellType) {
					case BLANK:
						rowData[c] = "";
						break;
					case BOOLEAN:
						rowData[c] = String.valueOf(cell.getBooleanCellValue());
						break;
					case ERROR:
						rowData[c] = FormulaError.forInt(cell.getErrorCellValue());
						break;
					case FORMULA:
						if (!page.isIncludeFormula()) {
							CellValue cv = evaluator.evaluate(cell);
							switch (cv.getCellTypeEnum()) {
							case BLANK:
								rowData[c] = "";
								break;
							case BOOLEAN:
								rowData[c] = String.valueOf(cell.getBooleanCellValue());
								break;
							case ERROR:
								rowData[c] = cv.formatAsString();
								break;
							case NUMERIC:
								rowData[c] = new Double(cv.getNumberValue());
								break;
							case STRING:
								rowData[c] = cv.getStringValue();
								break;
							default:
								rowData[c] = cv.formatAsString();
								break;
							}
						} else {
							rowData[c] = cell.getCellFormula();
						}
						break;
					case NUMERIC:
						rowData[c] = new Double(cell.getNumericCellValue());
						break;
					case STRING:
						rowData[c] = cell.getStringCellValue();
						break;
					default:
						rowData[c] = cell.getStringCellValue();
						break;
					}
				}
			}
			dbfWriter.addRecord(rowData);
		}

		DBFUtils.close(dbfWriter);
		if (fos != null)
			fos.close();
		files.put(to + DBF, filePath);
	}

	/**
	 * 将report对象转换为dbf字节导出到指定输出流中.(支持多张报表合并输出)
	 * @param report  report对象
	 * @param out  输出流
	 * @throws Exception
	 */
	public static void export(Report report, OutputStream out) throws Exception {
		Collection<String> tos = report.getToSheetNames();
		Map<String, String> files = new HashMap<String, String>();
		if (tos.size() > 1) {
			// 打成压缩包
			String zipfile = getTemporaryFile(TMP);
			for (String to : tos) {
				singleHandler(report, to, files);
			}
			zip(files, zipfile);
			File zip = new File(zipfile);
			InputStream in = new FileInputStream(zip);
			IOUtils.copy(in, out);
			in.close();
			for (String dbf : files.values()) {
				new File(dbf).delete();
			}
			zip.delete();
		} else {
			for (String to : tos) {
				singleHandler(report, to, files);
				break;
			}
			for (String dbf : files.values()) {
				IOUtils.copy(new FileInputStream(dbf), out);
				new File(dbf).delete();
			}
		}
	}

	public static String generateKey() {
		return UUID.randomUUID().toString().replaceAll("-", "").toUpperCase();
	}

	public static String getTemporaryFile(String suffix) {
		return getTemporaryPath() + "/" + generateKey() + suffix;
	}

	public static String getTemporaryPath() {
		return System.getProperty("java.io.tmpdir") + "/" + ".temp";
	}

	public static void zip(Map<String, String> files, String to) throws IOException {
		ZipArchiveOutputStream zip = null;
		try {
			File zipfile = new File(to);
			zip = new ZipArchiveOutputStream(zipfile);
			zip.setEncoding(ENCODING);
			zip.setUseZip64(Zip64Mode.AsNeeded);
			zip.setLevel(0);
			Set<String> keys = files.keySet();
			for (String key : keys) {
				File f = new File(files.get(key));
				if (f.exists() && f.isFile()) {
					ZipArchiveEntry entry = new ZipArchiveEntry(f, key);
					zip.putArchiveEntry(entry);
					try {
						byte[] data = FileUtils.readFileToByteArray(f);
						IOUtils.write(data, zip);
					} finally {
						zip.closeArchiveEntry();
					}
				}
			}
			zip.flush();
			zip.finish();
		} finally {
			zip.close();
		}
	}

	public static void main(String args[]) {
		DbfUtil dbf = new DbfUtil();
		try {
			try {
				dbf.testDBF();
			} catch (ParseException e) {
				e.printStackTrace();
			}
		} catch (DBFException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	public void testDBF() throws DBFException, IOException, ParseException {
		DBFField fields[] = new DBFField[4];

		fields[0] = new DBFField();
		fields[0].setName("emp_code");
		fields[0].setType(DBFDataType.CHARACTER);
		fields[0].setLength(14);

		fields[1] = new DBFField();
		fields[1].setName("emp_name");
		fields[1].setType(DBFDataType.CHARACTER);
		fields[1].setLength(20);

		fields[2] = new DBFField();
		fields[2].setName("salary");
		fields[2].setType(DBFDataType.NUMERIC);
		fields[2].setLength(12);
		fields[2].setDecimalCount(2);

		fields[3] = new DBFField();
		fields[3].setName("mydate");
		fields[3].setType(DBFDataType.DATE);

		DBFWriter writer = null;
		DBFReader reader = null;
		FileInputStream fis = null;
		FileOutputStream fos = null;
		File f = new File("d:/emp.dbf");
		f.createNewFile();
		try {
			SimpleDateFormat simpledateformat = new SimpleDateFormat("yyyyMMdd");

			// 开始写
			fos = new FileOutputStream(f);
			writer = new DBFWriter(fos, StandardCharsets.GBK);
			writer.setFields(fields);

			Object rowData[] = new Object[4];
			rowData[0] = "204班1模考试成绩";
			rowData[1] = "John";
			rowData[2] = new Double(5000);
			rowData[3] = simpledateformat.parse("2018-11-10");

			writer.addRecord(rowData);

			rowData = new Object[4];
			rowData[0] = "我是中国人";
			rowData[1] = "Lalit";
			rowData[2] = new Double(3400.00);
			rowData[3] = simpledateformat.parse("20181110");

			writer.addRecord(rowData);

			rowData = new Object[4];
			rowData[0] = "我是中国人";
			rowData[1] = "Rohit";
			rowData[2] = new Double(7350.012);
			rowData[3] = simpledateformat.parse("20181110");

			writer.addRecord(rowData);

			DBFUtils.close(writer);
			System.out.println("The dbf file product success!");

			// 开始读
			fis = new FileInputStream(f);
			reader = new DBFReader(fis, StandardCharsets.GBK);
			Object[] objects = null;
			for (; (objects = reader.nextRecord()) != null;) {
				System.out.println(Arrays.toString(objects));
			}
			DBFUtils.close(reader);

		} finally {
			DBFUtils.close(reader);
			DBFUtils.close(writer);
		}
	}

	private static boolean isAscii(String str) {
		for (int i = 0; i < str.length(); i++) {
			char c = str.charAt(i);
			if (c < 0x00 || c > 0xFF) {
				return false;
			} 
		}
		return true;
	}

	private static int getColLength(String str) {
		int length = 0;
		for (int i = 0; i < str.length(); i++) {
			char c = str.charAt(i);
			if (c < 0x00 || c > 0xFF) {
				length += 2;
			} else {
				length++;
			}
		}
		return length;
	}
}
